package it.webflora.dao;

import it.webflora.entity.Provincia;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class ProvinciaDao implements Serializable{
	
	/**
     * 
     */
    private static final long serialVersionUID = 8466831272627099954L;
	private int id;
	private String descrizione;
	private int idProvincia;
	private int idRegione;
	private String sigla;
	private PreparedStatement pstm = null;
	private Connection conn = null;
	private ResultSet resultSet = null;

	public ProvinciaDao(){}
	// Code Auto-generated 


	public ProvinciaDao(int id,String descrizione,int idProvincia,int idRegione,String sigla){
	this.id=id;
	this.descrizione=descrizione;
	this.idProvincia=idProvincia;
	this.idRegione=idRegione;
	this.sigla=sigla;
	}


	public void setId(int id){
	this.id=id;
	 }
	// Code Auto-generated 


	public int getId(){
	return id;
	 }
	// Code Auto-generated 



	public void setDescrizione(String descrizione){
	this.descrizione=descrizione;
	 }
	// Code Auto-generated 


	public String getDescrizione(){
	return descrizione;
	 }
	// Code Auto-generated 



	



	



	public void setSigla(String sigla){
	this.sigla=sigla;
	 }
	// Code Auto-generated 


	public String getSigla(){
	return sigla;
	 }
	// Code Auto-generated 



	public ResultSet resultSet(){
	String query="SELECT * FROM provincia";
	 try {
	 pstm=getPreparedStatement(query);
	resultSet=pstm.executeQuery();
	} catch (SQLException ex) {
	ex.printStackTrace(); }
	return resultSet;
	   }


	public void getInsertSave(){
	String query="INSERT INTO provincia VALUES(?,?,?,?,?)";

	 try {
	 pstm=getPreparedStatement(query);
	 pstm.setInt(1,getId());
	 pstm.setString(2,getDescrizione());
	 pstm.setInt(3,getIdProvincia());
	 pstm.setInt(4,getIdRegione());
	 pstm.setString(5,getSigla());
	pstm.executeUpdate();

	} catch (SQLException ex) {
	ex.printStackTrace(); }
	finally{
	closeSession();
	}
	   }



	public int getIdProvincia() {
		return idProvincia;
	}


	public void setIdProvincia(int idProvincia) {
		this.idProvincia = idProvincia;
	}


	public int getIdRegione() {
		return idRegione;
	}


	public void setIdRegione(int idRegione) {
		this.idRegione = idRegione;
	}


	public void saveUpdate(String id){
	String query="UPDATE provincia SET descrizione=?,id_provincia=?,id_regione=?,sigla=? WHERE id_provincia='"+id+"'";

	 try {
	 pstm=getPreparedStatement(query);

	 pstm.setString(1,getDescrizione());
	 pstm.setInt(2,getIdProvincia());
	 pstm.setInt(3,getIdRegione());
	 pstm.setString(4,getSigla());
	pstm.executeUpdate();

	} catch (SQLException ex) {
	ex.printStackTrace(); }
	finally{
	closeSession();
	}
	   }




	public void delete(String id){
	String query="DELETE FROM provincia WHERE id_provincia='"+id+"'";
	 try {
	 pstm=getPreparedStatement(query);
	pstm.executeUpdate();

	} catch (SQLException ex) {
	ex.printStackTrace(); }
	finally{
	closeSession();
	}
	   }




	public PreparedStatement getPreparedStatement(String query){
	try {

	Class.forName("com.mysql.jdbc.Driver");
	conn=DriverManager.getConnection("jdbc:mysql://localhost/webflora","root","");
	 pstm=conn.prepareStatement(query);
	 }
	catch (ClassNotFoundException e) {
	e.printStackTrace(); }
	catch (SQLException ex) {
	ex.printStackTrace(); }
	return pstm;}
	 



	public void closeSession(){
	try {
	conn.close();
	pstm.close();
	if(resultSet!=null){
	resultSet.close();
	}
	}catch (SQLException ex) {
	ex.printStackTrace(); }
	}

	
	
	
	public List  resultSetProvince(int idReg){
		
		List<Provincia> ListProvincia=new ArrayList<Provincia>();
		Provincia prov;
		String query="SELECT * FROM provincia where id_regione ='"+idReg+"'";
		 try {
		 pstm=getPreparedStatement(query);
		resultSet=pstm.executeQuery();
		
		while (resultSet.next())
		{
			prov=new Provincia();
			prov.setDescrizione(resultSet.getString("descrizione"));
			prov.setIdProvincia(resultSet.getInt("id_provincia"));
			prov.setIdRegione(resultSet.getInt("id_regione"));
			prov.setSigla(resultSet.getString("sigla"));
			ListProvincia.add(prov);
		}
		   
		
		
		} catch (SQLException ex) {
		ex.printStackTrace(); }
		return ListProvincia;
		   }

}
